﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace HRdex_Final.Announcement
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        DataTable dtbl = new DataTable("dtbl");

        protected void Page_Load(object sender, EventArgs e)
        {

            //Announcement Output Data
            string announcementID = Request.QueryString["AnnouncementID"];

            if (announcementID != null)
            {
                string sqlc = "SELECT formAnnouncement.Subject, formAnnouncement.Body, formAnnouncement.DateTime," +
               " formAnnouncement.Attachment, catAnnouncement.CatName " +
               " FROM formAnnouncement INNER JOIN catAnnouncement ON formAnnouncement.CatAnnID = catAnnouncement.CatAnnID " +
               "   WHERE        (formAnnouncement.AnnouncementID = " + announcementID + ")";
                SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=HRdexDB;Integrated Security=True");
                SqlDataAdapter cmd = new SqlDataAdapter(sqlc, conn);

                cmd.Fill(dtbl);
                txtAnnouncementID.Text = announcementID;
                //fuAttachment.FileName. = dtbl.Rows[0].ItemArray[3].ToString();
                txtDescription.Text = dtbl.Rows[0].ItemArray[1].ToString();
                lblPubDate.Text = dtbl.Rows[0].ItemArray[2].ToString();
                txtSubject.Text = dtbl.Rows[0].ItemArray[0].ToString();
                ddlCategories.SelectedValue = dtbl.Rows[0].ItemArray[4].ToString();
            }
        }



        protected void txtSubmit_Click(object sender, EventArgs e)
        {
            string announcementID = Request.QueryString["AnnouncementID"];
            //connection Add data
            SqlConnection conn = new SqlConnection("Data Source=(local); Initial Catalog=HRdexDB; Integrated Security=SSPI");
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();


            if (txtAnnouncementID.Text != announcementID)
            {
                conn.Open();
                //Add 

                SqlCommand cmd = new SqlCommand("Insert Into formAnnouncement (Subject, Body, CatAnnID, Attachment, DateTime) Values (@subject, @body, @catannID, @attachment, @datetime )", conn);
                
                Stream fs = fuAttachment.PostedFile.InputStream;
                BinaryReader br = new BinaryReader(fs);
                Byte[] bytes = br.ReadBytes((Int32)fs.Length);

                cmd.Parameters.Add("@subject", SqlDbType.VarChar).Value = txtSubject.Text;
                cmd.Parameters.Add("@body", SqlDbType.VarChar).Value = txtDescription.Text;
                cmd.Parameters.Add("@catannID", SqlDbType.Int).Value = ddlCategories.Text;
                cmd.Parameters.Add("@attachment", SqlDbType.Binary).Value = bytes;
                cmd.Parameters.Add("@datetime", SqlDbType.DateTime).Value = DateTime.Now;

                cmd.ExecuteNonQuery();

                conn.Close();
                fuAttachment.Visible = true;
                btnDownload.Visible = false;
            }
            else
            {
                EditHere();
                uploadedit();
            }
        }

        override protected void OnInit(EventArgs e)
        {
            InitializeComponent();
            base.OnInit(e);
        }

        private void InitializeComponent()
        {
            //this.cmdStore.Click += new System.EventHandler(this.cmdStore_Click);
            this.Load += new System.EventHandler(this.Page_Load);

        }

        protected void btnEdit_Click(object sender, ImageClickEventArgs e)
        {

            string announcementID = Request.QueryString["AnnouncementID"];


            txtAnnouncementID.Text = announcementID;
            txtSubject.Text = dtbl.Rows[0].ItemArray[0].ToString();
            txtDescription.Text = dtbl.Rows[0].ItemArray[1].ToString();
            //ddlCategories.Text = dtbl.Rows[0].ItemArray[4].ToString();
        }

        public void EditHere()
        {
            string announcementID = Request.QueryString["AnnouncementID"];
            string edit = ("UPDATE [formAnnouncement] SET [Subject] ='" + txtSubject.Text + "', [Body] ='" + txtDescription.Text + "' WHERE  ([AnnouncementID] = " + announcementID + ")");
            SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=HRdexDB;Integrated Security=True");
            SqlCommand cmd = new SqlCommand(edit, conn);
            SqlDataAdapter adapt = new SqlDataAdapter();
            conn.Open();


            cmd.Parameters.AddWithValue("Subject", txtSubject.Text);
            cmd.Parameters.AddWithValue("Body", txtDescription.Text);
            //cmd.Parameters.Add("CatAnnID", ddlCategories.Text);

            cmd.ExecuteNonQuery();
            conn.Close();
            fuAttachment.Visible = false;
            //lblDownload.Visible = true;
            //btnDownload.Visible = true;

        }

        private void uploadedit()
        {
            string filepath = fuAttachment.PostedFile.FileName;
            string filename = Path.GetFileName(filepath);
            //lblDownload.Text = filename;

            Stream fs = fuAttachment.PostedFile.InputStream;
            BinaryReader br = new BinaryReader(fs);
            Byte[] bytes = br.ReadBytes((Int32)fs.Length);

            string announcementID = Request.QueryString["AnnouncementID"];
            string edit = ("UPDATE formAnnouncement SET  Attachment = @Data WHERE  ([AnnouncementID] = " + announcementID + ")");
            SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=HRdexDB;Integrated Security=True");
            SqlCommand cmd = new SqlCommand(edit, conn);
            cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
            SqlDataAdapter adapt = new SqlDataAdapter();

            conn.Open();

            cmd.ExecuteNonQuery();
            conn.Close();


        }

        protected void btnDownload_Click(object sender, EventArgs e)
        {
            string announcementID = Request.QueryString["AnnouncementID"];
            string strQuery = "SELECT Attachment, Subject FROM formAnnouncement WHERE AnnouncementID = " + announcementID;
            SqlCommand cmd = new SqlCommand(strQuery);
            DataTable dt = GetData(cmd);
            if (dt != null)
            {
                download(dt);
            }
        }
        SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");
        private DataTable GetData(SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
            try
            {
                conn.Open();
                sda.SelectCommand = cmd;
                sda.Fill(dt);
                return dt;

            }
            catch
            {
                return null;
            }
            finally
            {
                conn.Close();
                sda.Dispose();
                conn.Dispose();
            }

        }
        private void download(DataTable dt)
        {
            Byte[] bytes = (Byte[])dt.Rows[0]["Attachment"];
            Response.Buffer = true;
            Response.Charset = "";
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            //Response.ContentType = dt.Rows[0]["FileType"].ToString();
            Response.AddHeader("content-disposition", "attachment;FileName="
            + dt.Rows[0]["Subject"].ToString());
            Response.BinaryWrite(bytes);
            Response.Flush();
            Response.End();
        } 
    }
}